1. Field of the Invention
The present invention relates generally to a data processing system. More specifically, the present invention provides a computer implemented method, computer program product, and data processing system for optimistic locking using SQL SELECT, UPDATE, DELETE, and INSERT statements.
2. Description of the Related Art
Optimistic Locking is a well-known Structured Query Language (SQL) programming technique used to maintain data integrity in a relational database within the scope of a database transaction without having simple read operations blocking write operations. The technique is generally described as including data obtained using a previously executed (in the same transaction) SELECT statement in the WHERE clause of an UPDATE statement in such a way that an intervening change (made by another concurrently executing program) in the row being updated would prevent the UPDATE statement from updating the row. When such an update is so prevented, the database transaction is rolled back and an appropriate recovery action is taken, for example, retrying the rolled back transaction.
It is often not practical to include in the WHERE clause values for all the columns whose data could possible change. Therefore, an optimistic locking technique that does not include in the WHERE clause all columns whose data could possibly change must arrange it so that whenever any of the data in any of the columns changes, at least one data value in the WHERE clause also changes. One technique that may be used to achieve this, that is obvious to one of ordinary skill in the art and used in this disclosure, is to define a new column, referred to as the OPTCOUNTER column in this disclosure, and to include that column in the WHERE clause, and to arrange that whenever any data value is changed the OPTCOUNTER value is also changed.
Additionally, this technique does not solve problems that arise when a DELETE operation followed by an INSERT operation is performed in the same database transaction. In order to maintain data integrity, this situation needs to be handled in such a way that a concurrent attempt to change the row being deleted and re-inserted can detect that the row has been changed since it was read, and can cause its transaction to be rolled back.
Furthermore, this technique does not address the case of an INSERT operation. Typically, constant values are assigned to columns whose values will be included in WHERE clauses of Optimistic SQL statements. Thus a problem arises when a DELETE of a row containing those constant values is followed by an INSERT in the same transaction. The problem is that the values in the deleted rows are not known and cannot be remembered so they will not be used in the INSERT statement. Thus data integrity is much more likely to be compromised since a concurrent attempt to change the row being deleted and re-inserted cannot detect that the row has been changed since it was read.